#!/bin/bash

# 定义变量方便修改
table_db=cy_database
hive=/home/software/hive-2.3.6/bin/hive

# 如果是输入的日期按照取输入日期；如果没输入日期取当前时间的前一天
if [ -n "$1" ] ;then
	do_date=$1
else
	do_date=`date -d "-1 day" +%F`
fi

# 分析预测-岗位活跃度榜单

sql="
use cy_database;

with
tmp_business_alive1 as
(
	select
		name,
		entry_work,
		out_work,
		ent_name,
		position,
		industry_code,
		business_code,
		round(rand()*5) as talents_type,
		dt
	from dwd_enterprise_person_info
),


tmp_business_alive2 as --单月已就业的人数
(
select
	industry_code,
	position as posts_name,
	ew_time,
	count(1) as entry_sum  --单月已就业人数
from (
	select
		name,
		date_format(entry_work,'yyyy-MM') as ew_time,
		position,
		industry_code
	from tmp_business_alive1
	)a
group by industry_code,position,ew_time
),


tmp_business_alive3 as  --单月高科技人才入职人数
(
select
	industry_code,
	position as posts_name,
	ew_time,
	count(1) as high_tech_talent_num  --单月高科技人才入职人数
from (
	select
		name,
		date_format(entry_work,'yyyy-MM') as ew_time,
		position,
		industry_code
	from tmp_business_alive1
	where talents_type = '0'
	)a
group by industry_code,position,ew_time


),


tmp_business_alive4_1 as --单月人才需求总量= 单月招聘日期最后更新的
(
select
	industry_code,
	posts_name,
	posts,--当月每个公司招聘职位最后日期还在招聘的人数
	mon_time
from (
	select
		ent_name,
		industry_code,
		posts_name,
		posts,
		update_time,
		mon_time,
		row_number() over(partition by industry_code,ent_name,posts_name,mon_time order by update_time desc) as rn
	from (
		select
			ent_name,
			industry_code,
			posts_name,
			posts,
			update_time,
			date_format(update_time,'yyyy-MM') as mon_time
		from dwd_enterprise_position_require_detail
		)a
	)aa
where rn =1 --取出最近的日期
),

tmp_business_alive4_2 as
(
select
	industry_code,
	posts_name,
	mon_time,
	sum(posts) as require_talent_num --单月人才需求总量
from tmp_business_alive4_1
group by industry_code,posts_name,mon_time
),

tmp_business_alive5 as --单月离职的人数
(
select
	industry_code,
	position as posts_name,
	ow_time,
	count(1) as quit_num --单月离职人数
from (
	select
		name,
		industry_code,
		position,
		date_format(out_work,'yyyy-MM') as ow_time,
		dt
	from tmp_business_alive1
	where out_work is not null
	)a
group by industry_code,position,ow_time

),


tmp_business_alive6 as --两个两个表先全连接
(
select
	ba2.industry_code,
	ba2.posts_name,
	ba2.ew_time,
	ba2.entry_sum,--单月已就业人数
	ba3.high_tech_talent_num,--单月高科技人才入职人数
	ba42.require_talent_num--单月人才需求总量
from tmp_business_alive2 ba2
full outer join tmp_business_alive3 ba3
on ba2.industry_code=ba3.industry_code and ba2.posts_name=ba3.posts_name and ba2.ew_time=ba3.ew_time
full outer join tmp_business_alive4_2 ba42
on ba2.industry_code=ba42.industry_code and ba2.posts_name=ba42.posts_name and ba2.ew_time=ba42.mon_time
),

tmp_business_alive7 as
(
select
	ba6.industry_code,
	ba6.posts_name,
	ba6.ew_time as operate_time,
	ba6.entry_sum,--单月已就业人数
	ba6.high_tech_talent_num,--单月高科技人才入职人数
	ba6.require_talent_num,--单月人才需求总量
	ba5.quit_num --单月离职人数
from tmp_business_alive6 ba6
full outer join tmp_business_alive5 ba5
on ba6.industry_code=ba5.industry_code and ba6.posts_name=ba5.posts_name and ba6.ew_time=ba5.ow_time
)

insert into table cy_database.dws_posts_alive
select
	industry_code,
	posts_name,
	high_tech_talent_num,
	require_talent_num,
	entry_sum,
	quit_num,
	operate_time
from tmp_business_alive7


;
"

$hive -e "$sql"